USE master
GO
IF EXISTS (SELECT * FROM sys.databases WHERE NAME = N'ExamOnline')
DROP DATABASE ExamOnline
GO
CREATE DATABASE ExamOnline
GO
USE ExamOnline
CREATE TABLE [Group]
(
	IDG int IDENTITY(1,1) PRIMARY KEY,
	Description nvarchar(100)
)
GO
CREATE TABLE Members
(
	UserName VARCHAR(256) PRIMARY KEY,
	IDG int FOREIGN KEY REFERENCES [Group](IDG) ON UPDATE CASCADE ON DELETE CASCADE,
	Password VARCHAR(256),
	FullName VARCHAR(256),
	Sex BIT,
	Birthday DATETIME,
	Telephone VARCHAR(20),
	Address NVARCHAR(256),
	Email VARCHAR(100),
	Status BIT
)
GO
CREATE TABLE FeedBack
(
	FeedBackID INT IDENTITY(1,1) PRIMARY KEY,
	UserName VARCHAR(256) FOREIGN KEY REFERENCES Members(UserName) ON UPDATE CASCADE ON DELETE CASCADE,
	Title NVARCHAR(150),
	[Content] NTEXT,
	Email VARCHAR(100),
	Telephone VARCHAR(20),
	DateSend DATETIME DEFAULT GETDATE(),
	Status BIT
)
GO
CREATE TABLE Subject
(
	SubjectID INT IDENTITY(1,1) PRIMARY KEY,
	SubjectName NVARCHAR(100),
	Status BIT
)
CREATE TABLE subSubject
(
	subID INT IDENTITY(1,1) PRIMARY KEY,
	SubjectID INT FOREIGN KEY REFERENCES Subject(SubjectID) ON UPDATE CASCADE ON DELETE CASCADE,
	subName NVARCHAR(256),
	Status BIT
	
)
GO
CREATE TABLE Questions
(
	QuestionID INT IDENTITY(1,1) PRIMARY KEY, 
	subID INT FOREIGN KEY REFERENCES subSubject(subID) ON UPDATE CASCADE ON DELETE CASCADE,
	ContentQuestion NTEXT,
	Mark FLOAT,
	Status BIT
)
GO
CREATE TABLE Answers
(
	AnswerID INT IDENTITY(1,1) PRIMARY KEY,
	QuestionID INT  FOREIGN KEY REFERENCES Questions(QuestionID) ON UPDATE CASCADE ON DELETE CASCADE,
	AnswerContent NTEXT,
	TrueOrFalse BIT
)
GO
CREATE TABLE Test
(
	TestID INT IDENTITY(1,1) PRIMARY KEY,
	subID INT FOREIGN KEY REFERENCES subSubject(subID) ON UPDATE CASCADE ON DELETE CASCADE,
	TestName NVARCHAR(256),
	TestTime INT,
	Status BIT
)
GO
CREATE TABLE Result
(
	ResultID INT IDENTITY(1,1) PRIMARY KEY,
	TestID INT FOREIGN KEY REFERENCES Test(TestID) ON UPDATE CASCADE ON DELETE CASCADE,
	UserName VARCHAR(256) FOREIGN KEY REFERENCES Members(UserName) ON UPDATE CASCADE ON DELETE CASCADE,
	StartTime DATETIME,
	Endtime DATETIME,
	AnswerTrue INT,
	Mark FLOAT ,
	Status BIT
)
GO
CREATE TABLE ResultDetail
(
	ResultID INT FOREIGN KEY REFERENCES Result(ResultID) ON UPDATE CASCADE ON DELETE CASCADE,
	QuestionID INT  FOREIGN KEY REFERENCES Questions(QuestionID),
	AnswerID INT FOREIGN KEY REFERENCES Answers(AnswerID)
)
CREATE TABLE Vote
(
	VoteID INT IDENTITY(1,1) PRIMARY KEY,
	VoteName NVARCHAR(256),
	CreateTime DATETIME,
	DateTime DATETIME,
	Status INT DEFAULT 1
)
CREATE TABLE VoteDetail
(
	SelectID INT IDENTITY(1,1) PRIMARY KEY,
	VoteID INT FOREIGN KEY REFERENCES Voice(VoiceID) ON UPDATE CASCADE ON DELETE CASCADE,
	VoteSelect NVARCHAR(256),
	SelectTed DECIMAL DEFAULT 0	
)



---BEGIN CREATE PROCEDURE
-- Member PROC
GO
CREATE PROC memberSelect AS
	BEGIN
		SELECT * FROM Members
	END
GO
CREATE PROC searchMember(@member VARCHAR(256)) AS
	BEGIN
		SELECT * FROM Members WHERE UserName = @member
	END
GO
CREATE PROC searchMemberLIKE(@member VARCHAR(256)) as
	BEGIN
		SELECT * FROM Members WHERE UserName LIKE '%'+@member+'%'
	END
GO
CREATE PROC GroupUpdate(@des nvarchar(100))	AS
	BEGIN
		UPDATE [Group] SET
			Description = @des
	END
GO

CREATE PROC MemberInsert(@user varchar(256), @idg int, @pass varchar(256), @fullname varchar(256), @sex bit, @birthday datetime, @tel varchar(20), @add nvarchar(256), @mail varchar(100), @status bit) AS
	BEGIN
		INSERT INTO Members(UserName,IDG,Password,FullName,Sex,Birthday,Telephone,Address,Email,Status) VALUES(@user, @idg, @pass, @fullname, @sex, @birthday, @tel, @add, @mail, @status)
	END
GO
CREATE PROC Memberupdate(@user varchar(256), @idg int, @pass varchar(256), @fullname varchar(256), @sex bit, @birthday datetime, @tel varchar(20), @add nvarchar(256), @mail varchar(100), @status bit) AS
	BEGIN
		UPDATE Members SET
			UserName = @user,
			IDG = @idg,
			Password = @pass,
			FullName = @fullname,
			Sex = @sex,
			Birthday = @birthday,
			Telephone = @tel,
			Address = @add,
			Email = @mail,
			Status = @status
		WHERE UserName = @user
	END
GO
CREATE PROC MemberDelete(@user varchar(256)) AS
	BEGIN
		DELETE FROM Members WHERE UserName = @user
	END
GO	
CREATE PROC InsertFeedback (@ID int, @user varchar(50), @title nvarchar(150), @content ntext,@mail varchar(100),@tel varchar(20), @send datetime, @status bit) AS
	BEGIN
		INSERT INTO FeedBack(FeedBackID, UserName, Title, [Content], Email, TelePhone, DateSend, Status) VALUES(@ID, @user, @title, @content,@mail,@tel @send, @status)
	END
GO
CREATE PROC FeedBackSelect AS

	BEGIN
		SELECT * FROM FeedBack
	END
GO
CREATE PROC DeleteFeedBack(@FeedID int) AS

	BEGIN
		DELETE FROM FeedBack WHERE FeedBackID = @FeedID
	END
--END Member
GO
CREATE PROC SubjectSelect AS
	BEGIN
		SELECT * FROM Subject
	END
GO
CREATE PROC SubjectInsert(@name nvarchar(100), @status bit)	AS
	BEGIN
		INSERT INTO Subject(SubjectName,Status) VALUES(@name,@status)
	END
GO


CREATE PROC SubjectUpdate(@name nvarchar(100), @status bit)	AS
	BEGIN
		UPDATE Subject SET
			SubjectName = @name,
			Status = @status
	END
GO
CREATE PROC subInsert(@SubjectID INT, @subName NVARCHAR(256), @Status BIT) AS
	BEGIN
		INSERT INTO subSubject
		(
			SubjectID,
			subName,
			[STATUS]
		)
		VALUES
		(
			@SubjectID,
			@subName,
			@Status
		)
	END 
GO


CREATE PROC QuestionSelect AS
	BEGIN
		SELECT * FROM Questions
	END
GO


CREATE PROC QuestionSelectBySubject(@subID int)	AS
	BEGIN
		SELECT * FROM Questions WHERE subID = @subID
	END
GO

CREATE PROC QuestionInsert(@sID int, @Content ntext, @mark float, @status bit )	AS
	BEGIN
		INSERT INTO Questions(subID,ContentQuestion,Mark,Status) VALUES(@sID,@Content,@mark,@status)
	END
GO
/*
	exec SubjectInsert 'C' ,1
	exec SubjectInsert 'Java ',1
	exec SubjectInsert 'Database',1
	exec SubjectInsert '. Ner',1
------------------------------------- 
exec subInsert 1,'C Sharp',1
exec subInsert 1,'C++',1
exec subInsert 2,'A Java ',1
exec subInsert 2,'D Java',1
exec subInsert 2,'S Java',1
exec subInsert 2,'JSP',1
exec subInsert 3,'SQL',1
exec subInsert 3,'SDO',1
exec subInsert 4,'VB.Net',1
exec subInsert 4,'ASP.Net',1
exec subInsert 4,'C#.Net',1
GO
----------------------------------------------------------------------------------------------------*
exec QuestionInsert 1,'NET is said to accelerate the next generation of the Internet ?',0.5,1
exec QuestionInsert 1,'The unique feature of .NET is the ___________support that it provides ?',0.5,1
exec QuestionInsert 1,'.NET is a whole new platform centered around the Intranet ?',0.5,1
exec QuestionInsert 1,'A program in .NET is first compiled by the language specific compiler into __________________  ?',1,1
exec QuestionInsert 1,'What is the role of the CLR ?(Select all that apply) ',2,1
exec QuestionInsert 1,'Microsoft .NET is primarily made up of the following three components ? ',2,1
exec QuestionInsert 1,'Select the two core technologies on which the .NET platform is based ? ',2,1
exec QuestionInsert 1,'Microsoft .NET allows developers to develop applications using different languages, which run on the Unix platform ? ',1,1
exec QuestionInsert 1,'The .NET platform is built on Internet Protocols such as _______ and  _______? ',1,1
exec QuestionInsert 1,'The .NET platform is built on the following features of the Windows 2000 server family ?(Select all that apply) ',1,1
exec QuestionInsert 1,'Select the core .NET Enterprise Servers ? ',0.5,1
exec QuestionInsert 1,'Core Microsoft .NET building block services ? ',1,1
exec QuestionInsert 1,'_______ service allows users to handle their own rules for handling ? ',1,1
exec QuestionInsert 1,'Select the service, which allows users to maintain their schedules thus facilitating timely and manageable interactions with other users. ? ',1,1
exec QuestionInsert 1,'________ allows developers and business analysts work together to define and modify business processes shared between applications.? ',1,1
exec QuestionInsert 1,'Microsoft .Net was formerly known as __________ ? ',1,1
exec QuestionInsert 1,'C# allows _______ use of native pointers ? ',1,1
exec QuestionInsert 1,'What is the correct syntax for comment entries in C# ? ',1,1
exec QuestionInsert 1,'The public keyword can be ignored for the Main function in C# ? ',1,1
exec QuestionInsert 1,'C# program can have only one using directive? ',1,1
exec QuestionInsert 1,'The WriteLine method is a part of the ______ class ? ',1,1
exec QuestionInsert 1,'C# is considered as a modern replacement for the language/s like ? ',1,1
exec QuestionInsert 1,'C# is a ____________language? ',1,1
exec QuestionInsert 1,'Manual memory management needs to be done in C# ? ',1,1
exec QuestionInsert 1,'Access Modifiers for variables in C# can be the following ? (Select all that apply) ',1,1
GO
*/
CREATE PROC AnswerInsert(@QuestionID INT, @AnswerContent NTEXT, @Status BIT)	AS
	BEGIN
		INSERT INTO Answers(QuestionID, AnswerContent, TrueOrFalse) VALUES(@QuestionID, @AnswerContent, @Status)
	END
GO
/*
exec AnswerInsert 26,'True',1
exec AnswerInsert 26,'False',0

exec AnswerInsert 27,'Multi-platform',0
exec AnswerInsert 27,'Multi-language',0

exec AnswerInsert 28,'True',0
exec AnswerInsert 28,'False',1

exec AnswerInsert 29,'Common Language ',0
exec AnswerInsert 29,'Runtime Language',0
exec AnswerInsert 29,'Intermediate Language ',1
exec AnswerInsert 29,'Visual Basic',0

exec AnswerInsert 30,'Manages memory',1
exec AnswerInsert 30,'Compiles program into machine code',0
exec AnswerInsert 30,'Compiles program to a .exe file ',0
exec AnswerInsert 30,'Compile once and run on any CPU & OS that supports the runtime ',0

exec AnswerInsert 31,'Visual Studio .NET',1
exec AnswerInsert 31,'3rd party .NET services',1
exec AnswerInsert 31,'.NET platform itself ',1
exec AnswerInsert 31,'Microsoft .NET products and services',0

exec AnswerInsert 32,'XML',1
exec AnswerInsert 32,'WML',0
exec AnswerInsert 32,'Internet Protocols',1
exec AnswerInsert 32,'Internet computing ',0

exec AnswerInsert 33,'True',1
exec AnswerInsert 33,'False',0

exec AnswerInsert 34,'TCP /IP',1
exec AnswerInsert 34,'SOAP ',0
exec AnswerInsert 34,'HTTP',1
exec AnswerInsert 34,'IP',0

exec AnswerInsert 35,'Reliability ',1
exec AnswerInsert 35,'Security  ',1
exec AnswerInsert 35,'Scalability',0
exec AnswerInsert 35,'Manageability',1

exec AnswerInsert 36,'Commerce Server 2000',1
exec AnswerInsert 36,'Exchange 2000 Server ',1
exec AnswerInsert 36,'Apple Server ',0
exec AnswerInsert 36,'Visual .Net Server',0

exec AnswerInsert 37,'Calendar',1
exec AnswerInsert 37,'Dynamic delivery ',1
exec AnswerInsert 37,'Dynamic Service ',0
exec AnswerInsert 37,'Notification ',1

exec AnswerInsert 38,'Notification',0
exec AnswerInsert 38,'Personalization ',1

exec AnswerInsert 39,'Dynamic Service',0
exec AnswerInsert 39,'Personalization ',0
exec AnswerInsert 39,'Notification ',0
exec AnswerInsert 39,'Calendar ',1

exec AnswerInsert 40,'Orchestration',1
exec AnswerInsert 40,'Web Service  ',0
exec AnswerInsert 40,'Common Language Runtime ',0
exec AnswerInsert 40,'.Net Framework',0

exec AnswerInsert 41,'NGWS (Next Generation Windows Services)',1
exec AnswerInsert 41,'NGUS ',0
exec AnswerInsert 41,'MGWS ',0
exec AnswerInsert 41,'NWGS',0

exec AnswerInsert 42,'Public',1
exec AnswerInsert 42,'Private ',0
exec AnswerInsert 42,'Complete ',0
exec AnswerInsert 42,'Restricted',0

exec AnswerInsert 43,'//....// ',0
exec AnswerInsert 43,'/**...**/ ',0
exec AnswerInsert 43,'/*...*/',1
exec AnswerInsert 43,'/.../',0

exec AnswerInsert 44,'True',1
exec AnswerInsert 44,'False',0

exec AnswerInsert 45,'True',0
exec AnswerInsert 45,'False',1

exec AnswerInsert 46,'System',0
exec AnswerInsert 46,'System.Output  ',0
exec AnswerInsert 46,'Console',1
exec AnswerInsert 46,'Console.System',0

exec AnswerInsert 47,'C',1
exec AnswerInsert 47,'Java',0
exec AnswerInsert 47,'C++',1
exec AnswerInsert 47,'VB',0

exec AnswerInsert 48,'purely Procedure-Oriented',0
exec AnswerInsert 48,'Procedure-Oriented and Object-Oriented ',0
exec AnswerInsert 48,'purely Object-Oriented',1
exec AnswerInsert 48,'partially Procedure-Oriented ',0

exec AnswerInsert 49,'True',0
exec AnswerInsert 49,'False',1

exec AnswerInsert 50,'public',1
exec AnswerInsert 50,'protected',1
exec AnswerInsert 50,'private',1
exec AnswerInsert 50,'public protected ',0
exec AnswerInsert 50,'private protected ',0
GO
*/

CREATE PROC TestInsert(@subID int,@testName NVARCHAR(256), @time int, @status bit)	AS
	BEGIN
		INSERT INTO Test(subID,TestName,TestTime,Status) VALUES(@subID,@testName,@time,@status)
	END
GO

CREATE PROC ResultInsert(@testID int, @user varchar(256),@startTime datetime, @endtime datetime, @answerT int, @status bit) AS
	BEGIN 
		INSERT INTO Result(TestID,UserName,StartTime,EndTime,AnswerTrue,Status) VALUES(@testID,@user,@startTime,@endtime,@answerT,@status)
	END
GO

CREATE PROC ResultDetailInsert(@ResultID INT, @questionID int, @answerID int) AS
	BEGIN
		INSERT INTO ResultDetail(ResultID,QuestionID,AnswerID) VALUES(@ResultID ,@questionID,@answerID)
	END
GO

-------------------------END--------------------
--__START TEST PROC_______________________________


/*
EXEC GroupInsert 'Administrator'
EXEC GroupInsert 'Members'

EXEC MemberInsert 'phongtn',1,'namphong','Phong Tn',1,'10/29/1988','0904168681','Nhan Chinh - Ha Noi','phongtn.group1@gmail.com',1
EXEC MemberInsert 'tester',2,'123456','Nguyen Thu Ha',0,'6/13/1985','0904456456','Hoan Kiem - Ha Noi','abcde@gmail.com',1
EXEC MemberUpdate 'tester',2,'123456','Nguyen Thu Ha',0,'6/13/1985','0904456456','Hoan Kiem - Ha Noi','abcde@gmail.com',1

EXEC TestInsert 1,N'test vui',30,30,1
EXEC TestTedInsert 1,'phongtn','10/10/2009','10/10/2009',20,1 

EXEC TestDetailInsert 2,1,3



SELECT * FROM Test
SELECT * FROM TestTedDetail
SELECT * FROM QUESTIONS
SELECT * FROM Answers
Select AnswerContent from Answers where AnswerId =1
Select ContentQuestion from Questions where QuestionId =5

SELECT * FROM Answers a , Questions q where a.questionID= q.QuestionID and TrueOrFalse =1

SELECT UserName,StartTime,EndTime,AnswerTrue,TestName,TestTime,NumBerQuestion FROM Test te JOIN TestTed tt ON te.TestID = tt.TestID

SELECT CURRENT_TIMESTAMP 
SELECT GETDATE() AS DATE
SELECT {fn NOW()}

SELECT CONVERT(VARCHAR,StartTime,110) FROM TestTed
*/


 